use std::collections::HashMap;

use chrono::{NaiveDate, NaiveDateTime};
use num_traits::cast::ToPrimitive;
use sea_orm::{ConnectionTrait, DatabaseConnection, QueryResult, Statement};
use serde::{Deserialize, Serialize};

use crate::{JsonMap, JsonValue};

#[derive(Debug, Serialize, Deserialize)]
#[allow(non_camel_case_types)]
pub enum DateType {
    i8,
    i16,
    i32,
    i64,
    u8,
    u16,
    u32,
    u64,
    float,
    decimal,
    bool,
    string,
    date,
    datetime,
    json,
}

impl DateType {
    pub fn map_value(
        &self,
        q: &QueryResult,
        key: &str,
    ) -> Result<serde_json::Value, sea_orm::error::DbErr> {
        match self {
            DateType::i8 => q.try_get_by::<Option<i8>, &str>(key).map(JsonValue::from),
            DateType::i16 => q.try_get_by::<Option<i16>, &str>(key).map(JsonValue::from),
            DateType::i32 => q.try_get_by::<Option<i32>, &str>(key).map(JsonValue::from),
            DateType::i64 => q.try_get_by::<Option<i64>, &str>(key).map(JsonValue::from),
            DateType::u8 => q.try_get_by::<Option<u8>, &str>(key).map(JsonValue::from),
            DateType::u16 => q.try_get_by::<Option<u16>, &str>(key).map(JsonValue::from),
            DateType::u32 => q.try_get_by::<Option<u32>, &str>(key).map(JsonValue::from),
            DateType::u64 => q.try_get_by::<Option<u64>, &str>(key).map(JsonValue::from),
            DateType::float => q.try_get_by::<Option<f64>, &str>(key).map(JsonValue::from),
            DateType::decimal => q
                .try_get_by::<Option<sea_orm::prelude::Decimal>, &str>(key)
                .map(|f| f.and_then(|f| f.to_f64()))
                .map(JsonValue::from),
            DateType::bool => q.try_get_by::<Option<bool>, &str>(key).map(JsonValue::from),
            DateType::string => q
                .try_get_by::<Option<String>, &str>(key)
                .map(JsonValue::from),
            DateType::json => q
                .try_get_by::<Option<JsonValue>, &str>(key)
                .map(JsonValue::from),
            DateType::date => q
                .try_get_by::<Option<NaiveDate>, &str>(key)
                .map(|d| d.map(|d| d.to_string()))
                .map(JsonValue::from),
            DateType::datetime => q
                .try_get_by::<Option<NaiveDateTime>, &str>(key)
                .map(|d| d.map(|d| d.to_string()))
                .map(JsonValue::from),
        }
    }
}

#[derive(Debug, Serialize, Deserialize)]
pub struct SqlModel {
    sql: String,
    data: HashMap<String, DateType>,
    #[serde(default)]
    values: Vec<String>,
    #[serde(default)]
    list: bool,
}

impl SqlModel {
    pub async fn exec_sql<F, V>(
        &self,
        conn: &DatabaseConnection,
        sql: &str,
        map_key: F,
    ) -> Result<JsonValue, sea_orm::error::DbErr>
    where
        F: Fn(&str) -> V,
        V: Into<sea_query::Value>,
    {
        let values: Vec<sea_query::Value> = self.values.iter().map(|v| map_key(v).into()).collect();
        let db_backend = conn.get_database_backend();
        let stmt = Statement::from_sql_and_values(db_backend, sql, values);
        if self.list {
            let q: Vec<QueryResult> = conn.query_all(stmt).await?;
            let datas = q
                .iter()
                .map(|q| self.map_query(&q))
                .flatten()
                .collect::<Vec<_>>();
            Ok(JsonValue::Array(datas))
        } else {
            if let Some(q) = conn.query_one(stmt).await? {
                self.map_query(&q)
            } else {
                Ok(JsonValue::Null)
            }
        }
    }

    #[inline]
    pub async fn exec<F, V>(
        &self,
        conn: &DatabaseConnection,
        map_key: F,
    ) -> Result<JsonValue, sea_orm::error::DbErr>
    where
        F: Fn(&str) -> V,
        V: Into<sea_query::Value>,
    {
        self.exec_sql(conn, &self.sql, map_key).await
    }

    fn map_query(&self, q: &QueryResult) -> Result<JsonValue, sea_orm::prelude::DbErr> {
        let mut result = JsonMap::new();
        for (key, ty) in &self.data {
            result.insert(key.to_owned(), ty.map_value(&q, key)?);
        }
        Ok(JsonValue::Object(result))
    }

    pub fn sql(&self) -> &str {
        &self.sql
    }
}
